import { PrismaClient, Prisma } from "@prisma/client";

const prisma = new PrismaClient();

//使用 $queryRaw 执行 SELECT 等返回数据的 SQL 语句：
async function runRawQuery() {
  // 1. 基础查询（无参数）
  const users: Prisma.UserSelect[] = await prisma.$queryRaw`
    SELECT id, name, email FROM User  
  `;
  console.log("Users:", users);
  //   users.forEach((user) => console.log(user.name));

  // 2. 带参数的查询（防止 SQL 注入）
  const email = "alice@example.com";
  const user = await prisma.$queryRaw`
    SELECT * FROM User WHERE email = ${email}
  `;
  console.log("User with email:", user);

  // 3. 类型安全的查询（推荐）
  // 使用 Prisma.sql 包裹并指定返回类型
  const typedUsers = await prisma.$queryRaw<Prisma.UserSelect[]>`
    SELECT * FROM User LIMIT 10
  `;
  // 此时 typedUsers 会有完整的类型提示
  typedUsers.forEach((user) => console.log(user.name));

  const posts: Prisma.PostSelect[] = await prisma.$queryRaw`select * from Post`;
  console.log("Posts:", posts);
}

// 使用 $executeRaw 执行 INSERT/UPDATE/DELETE 等不需要返回数据的 SQL 语句：

async function runRawCommand() {
  // 1. 更新数据
  const updatedCount = await prisma.$executeRaw`
    UPDATE Post SET published = true WHERE authorId = 2
  `;
  console.log(`Updated ${updatedCount} posts`);

  // 2. 带参数删除
  const authorId = 2;
  const deletedCount = await prisma.$executeRaw`
    DELETE FROM Post WHERE authorId = ${authorId}
  `;
  console.log(`Deleted ${deletedCount} posts`);
}
// 类型安全处理
// 定义查询结果的类型（与数据库表结构对应）
async function constumeQueryResult() {
  type PostWithAuthor = {
    id: number;
    title: string;
    authorName: string;
  };
  // 执行联表查询并指定类型
  const results = await prisma.$queryRaw<PostWithAuthor[]>`
  SELECT p.id, p.title, u.name as authorName
  FROM Post p
  JOIN User u ON p.authorId = u.id
`;
  console.log("Posts with author:", results);
  // 此时 results 会有类型提示
  results.forEach((item) => {
    console.log(`${item.title} by ${item.authorName}`);
  });
}

async function runRawQueryWithTransaction() {
  await prisma.$transaction([
    prisma.$executeRaw`UPDATE Post SET published = true WHERE id = 2`,
    prisma.$executeRaw`UPDATE Post SET published = true  `,
  ]);
  console.log("Transaction complete");
}

async function main() {
  //   await runRawQuery();
  //   await runRawCommand();
  //   await constumeQueryResult();
  await runRawQueryWithTransaction();
}

main()
  .catch((e) => {
    console.error(e);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });
